package cn.pconline.autoclub.service;

import cn.pconline.autoclub.entity.DynaInfo;
import cn.pconline.autoclub.entity.Subject;
import cn.pconline.autoclub.entity.SubjectPhoto;
import cn.pconline.autoclub.util.DateUtils;
import cn.pconline.autoclub.util.EncodeUtils;
import cn.pconline.autoclub.util.Pager;
import cn.pconline.autoclub.util.StringUtil;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collections;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import org.apache.commons.lang.StringUtils;
import org.gelivable.dao.GeliDao;
import org.gelivable.dao.GeliOrm;
import org.gelivable.dao.SqlBuilder;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;

/**
 * 话题service
 *
 * @author fgs
 *
 */
public class SubjectService {

    @Autowired
    private GeliOrm geliOrm;
    @Autowired
    private GeliDao geliDao;
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Autowired
    private SubjectDynaService subjectDynaService;
    @Autowired
    private UserSubjectService userSubjectService;
    @Autowired
    private PagerService pagerService;
    @Autowired
    private EffectiveDynaService dynaService;
    private static final Logger log = LoggerFactory.getLogger(SubjectService.class);

    /**
     * 创建话题（主表）
     *
     * @param subject
     * @return
     */
    private long createSubject(Subject subject) {
        long id = geliDao.create(subject);
        return id;
    }

    /**
     * 创建话题（第一次发表#XXX#的说说）
     *
     * @param subject
     * @param dynaInfoId
     * @return
     */
    public boolean createSubject(Subject subject, long dynaInfoId) {
        boolean flag = false;
        try {
            this.createSubject(subject);
            userSubjectService.createUserSubject(subject, dynaInfoId, Subject.TOP_NO);
            subjectDynaService.createDynaSubject(subject, dynaInfoId, Subject.TOP_NO);
            flag = true;
        } catch (Exception e) {
            log.error("create Subject throws a Exception", e);
        }
        return flag;
    }

    /**
     * 参与话题（不是第一次发表#XXX#的说说）
     *
     * @param subject
     * @param dynaInfoId
     * @return
     */
    public boolean addSubject(Subject subject, long dynaInfoId) {
        boolean flag = false;
        try {
            userSubjectService.createUserSubject(subject, dynaInfoId, Subject.TOP_NO);
            subjectDynaService.createDynaSubject(subject, dynaInfoId, Subject.TOP_NO);
            flag = true;
        } catch (Exception e) {
            log.error("add Subject throws a Exception", e);
        }
        return flag;
    }

    /**
     * 更新话题
     *
     * @param subject
     * @param fields
     * @return
     */
    public int updateByField(Subject subject, String fields) {
        return geliDao.update(subject, fields);
    }
    /**
     * 批量插入导语图片
     * @param photoList
     * @return 
     */
    public boolean batchCreatePhoto(List<SubjectPhoto> photoList){
        if(photoList != null && !photoList.isEmpty()){
            SqlBuilder sql = new SqlBuilder();
            sql.appendSql("insert into ").appendSql(geliOrm.getTableName(SubjectPhoto.class)).appendSql(" values(?,?,?,?)");
            List<Object[]> params = new ArrayList<Object[]>();
            for(SubjectPhoto photo : photoList){
                Object[] obj = new Object[]{geliDao.generateId(SubjectPhoto.class),photo.getSubjectId(),photo.getUrl(),photo.getSeq()};
                params.add(obj);
            }
            return jdbcTemplate.batchUpdate(sql.getSql(), params).length>0;
        }
        return true;
    }
    /**
     * 根据话题ID清除活题图片
     * @param subjectId
     * @return 
     */
    public boolean deleteSubjectPhoto(long subjectId){
        SqlBuilder sql = new SqlBuilder();
        sql.appendSql("delete from ").appendSql(geliOrm.getTableName(SubjectPhoto.class)).appendSql(" where subject_id=").appendValue(subjectId);
        return jdbcTemplate.update(sql.getSql(), sql.getValues())>0;
    }
    /**
     * 根据id查找话题（主话题）
     *
     * @param id
     * @return
     */
    public Subject findSubjectById(long id) {
        try {
            Subject subject = geliDao.find(Subject.class, id);
            if (subject.getStatus() == Subject.STATUS_NORMAL) {
                return subject;
            }
        } catch (EmptyResultDataAccessException e) {
            log.info("can't find Subject in database which id is {}", id);
        }
        return null;
    }

    private Pager<Subject> pageSubject(SqlBuilder sql, String orderSql, int pageNo, int pageSize) {
        sql.appendSql(orderSql);
        Pager<Subject> pager = pagerService.pager(Subject.class, sql, pageNo, pageSize);
        return pager;
    }

    /**
     * 根据阅读量倒叙分页查找
     *
     * @param pageNo
     * @param pageSize
     * @return
     */
    public Pager<Subject> pageSubjectByHot(int pageNo, int pageSize) {
        SqlBuilder sql = new SqlBuilder();
        sql.appendSql("select subject_id from ").appendSql(geliOrm.getTableName(Subject.class))
                .appendSql(" where status = ").appendValue(Subject.STATUS_NORMAL);
        String orderSql = " order by pv_count desc,create_at desc";
        return this.pageSubject(sql, orderSql, pageNo, pageSize);
    }
    
    /**
     * 根据阅读量倒叙分页查找（查询本周话题用到）
     *
     * @param pageNo
     * @param pageSize
     * @param beginDate
     * @return
     */
    public Pager<Subject> pageSubjectByHot(int pageNo, int pageSize, Date beginDate) {
        SqlBuilder sql = new SqlBuilder();
        sql.appendSql("select subject_id from ").appendSql(geliOrm.getTableName(Subject.class))
                .appendSql(" where status = ").appendValue(Subject.STATUS_NORMAL)
                .appendSql(" and create_at >=").appendValue(beginDate);
        String orderSql = " order by pv_count desc,create_at desc";
        return this.pageSubject(sql, orderSql, pageNo, pageSize);
    }

    /**
     * 根据创建时间倒叙分页查找
     *
     * @param pageNo
     * @param pageSize
     * @return
     */
    public Pager<Subject> pageSubjectByNew(int pageNo, int pageSize) {
        SqlBuilder sql = new SqlBuilder();
        sql.appendSql("select subject_id from ").appendSql(geliOrm.getTableName(Subject.class))
                .appendSql(" where status = ").appendValue(Subject.STATUS_NORMAL);
        String orderSql = " order by create_at desc";
        return this.pageSubject(sql, orderSql, pageNo, pageSize);
    }

    /**
     * 查询动态公共方法
     *
     * @param sql
     * @param orderSql
     * @param selectSql
     * @param pageNo
     * @param pageSize
     * @return
     */
    protected Pager<DynaInfo> getDynaListBySql(SqlBuilder sql, String orderSql, String selectSql, int pageNo, int pageSize) {
        int total = jdbcTemplate.queryForInt(sql.getSql().replace(selectSql, "select count(1) "), sql.getValues());
        if (total == 0) {
            return null;
        }
        Pager<DynaInfo> pager = new Pager<DynaInfo>();
        pager.setPageNo(pageNo);
        pager.setPageSize(pageSize);
        pager.setTotal(total);
        if (total == 0 || pageNo > pager.getPageCount() || (pageNo - 1) * pageSize < 0) {
            return null;
        }
        sql.appendSql(orderSql);
        sql.appendSql(" limit ").appendValue((pageNo - 1) * pageSize).appendSql(",").appendValue(pageSize);
        List<DynaInfo> list = jdbcTemplate.query(sql.getSql(), sql.getValues(), new ResultSetExtractor<List<DynaInfo>>() {
            @Override
            public List<DynaInfo> extractData(ResultSet rs) throws SQLException, DataAccessException {
                List<DynaInfo> dynaList = new ArrayList<DynaInfo>();
                while (rs.next()) {
                    long dynaInfoId = rs.getLong("dyna_info_id");
                    Date createDate = rs.getDate("create_at");
                    DynaInfo info = new DynaInfo();
                    info.setDynaInfoId(dynaInfoId);
                    info.setCreateDate(createDate);
                    dynaList.add(info);
                }
                return dynaList;
            }
        });
        //对查询结果可以按时间分组，批量去查。大部分情况，查询的数据应该是在同一张表的，可以减少查询
        Map<Integer, List<Long>> map = new LinkedHashMap<Integer, List<Long>>();
        for (DynaInfo info : list) {
            int year = DateUtils.getYear(info.getCreateDate());
            List<Long> ids = map.get(year);
            if (ids == null) {
                ids = new ArrayList<Long>();
                map.put(year, ids);
            }
            ids.add(info.getDynaInfoId());
        }
        List<DynaInfo> dynaList = new ArrayList<DynaInfo>(list.size());
        for (Integer year : map.keySet()) {
            List<Long> ids = map.get(year);
            Calendar cal = Calendar.getInstance();
            cal.set(Calendar.YEAR, year);
            List<DynaInfo> li = dynaService.findDynaByIds(cal.getTime(), ids.toArray());
            if (li == null || li.isEmpty()) {
                log.error("可能有脏数据！[{},{}]全部动态信息不存在", StringUtil.toString(ids.toArray()), cal.getTime());
                continue;
            } else {
                dynaList.addAll(li);
            }
        }
        if (dynaList.isEmpty()) {
            return null;
        }
        pager.setResultList(dynaList);
        return pager;
    }
    
    /**
     * 查询话题动态
     * @param sql
     * @param orderSql
     * @return
     */
    protected List<DynaInfo> listDynaListBySql(SqlBuilder sql, String orderSql) {
    	sql.appendSql(orderSql);
        List<DynaInfo> list = jdbcTemplate.query(sql.getSql(), sql.getValues(), new ResultSetExtractor<List<DynaInfo>>() {
            @Override
            public List<DynaInfo> extractData(ResultSet rs) throws SQLException, DataAccessException {
                List<DynaInfo> dynaList = new ArrayList<DynaInfo>();
                while (rs.next()) {
                    long dynaInfoId = rs.getLong("dyna_info_id");
                    Date createDate = rs.getDate("create_at");
                    DynaInfo info = new DynaInfo();
                    info.setDynaInfoId(dynaInfoId);
                    info.setCreateDate(createDate);
                    dynaList.add(info);
                }
                return dynaList;
            }
        });
      //对查询结果可以按时间分组，批量去查。大部分情况，查询的数据应该是在同一张表的，可以减少查询
        Map<Integer, List<Long>> map = new LinkedHashMap<Integer, List<Long>>();
        for (DynaInfo info : list) {
            int year = DateUtils.getYear(info.getCreateDate());
            List<Long> ids = map.get(year);
            if (ids == null) {
                ids = new ArrayList<Long>();
                map.put(year, ids);
            }
            ids.add(info.getDynaInfoId());
        }
        List<DynaInfo> dynaList = new ArrayList<DynaInfo>(list.size());
        for (Integer year : map.keySet()) {
            List<Long> ids = map.get(year);
            Calendar cal = Calendar.getInstance();
            cal.set(Calendar.YEAR, year);
            List<DynaInfo> li = dynaService.findDynaByIds(cal.getTime(), ids.toArray());
            if (li == null || li.isEmpty()) {
                log.error("可能有脏数据！[{},{}]全部动态信息不存在", StringUtil.toString(ids.toArray()), cal.getTime());
                continue;
            } else {
                dynaList.addAll(li);
            }
        }
        return dynaList;
    }

    /**
     * 根据userId和阅读量倒叙分页查找
     *
     * @param pageNo
     * @param pageSize
     * @return
     */
    public Pager<Subject> pageMySubjectByUserId(int pageNo, int pageSize, long userId) {
        SqlBuilder sql = new SqlBuilder();
        sql.appendSql("select subject_id from ").appendSql(geliOrm.getTableName(Subject.class))
                .appendSql(" where status = ").appendValue(Subject.STATUS_NORMAL)
                .appendSql(" and create_by = ").appendValue(userId);
        String orderSql = " order by pv_count desc,create_at desc";
        return this.pageSubject(sql, orderSql, pageNo, pageSize);
    }

    /**
     * 根据话题标题判断话题是否存在
     *
     * @param title
     * @return
     */
    public boolean isExistByTitle(String title) {
        SqlBuilder sql = new SqlBuilder();
        sql.appendSql("select count(1) from ").appendSql(geliOrm.getTableName(Subject.class));
        sql.appendSql(" where title = ").appendValue(title);
        long num = geliDao.count(sql.getSql(), sql.getValues());
        return num > 0;
    }

    /**
     * 根据话题标题查找话题
     *
     * @param title
     * @return
     */
    public Subject findByTitle(String title) {
        SqlBuilder sql = new SqlBuilder();
        sql.appendSql("select subject_id from ").appendSql(geliOrm.getTableName(Subject.class));
        sql.appendSql(" where title = ").appendValue(title);

        List<Subject> list = geliDao.list(Subject.class, sql.getSql(), sql.getValues());
        if (list.isEmpty()) {
            return null;
        } else {
            return list.get(0);
        }
    }

    /**
     * 按阅读数列出排行前num的话题
     *
     * @param num
     * @return
     */
    public List<Subject> listSubject(int num) {
        SqlBuilder sql = new SqlBuilder();
        sql.appendSql("select subject_id from ").appendSql(geliOrm.getTableName(Subject.class));
        sql.appendSql(" order by pv_count desc,title asc");
        try {
            return geliDao.page(Subject.class, sql.getSql(), 1, num, sql.getValues());
        } catch (EmptyResultDataAccessException e) {
            return Collections.emptyList();
        }
    }

    /**
     * 根据ID查话题
     *
     * @param ids
     * @return
     */
    public List<Subject> listSubjectByIds(Object... ids) {
        try {
            return geliDao.list(Subject.class, ids);
        } catch (EmptyResultDataAccessException e) {
            return Collections.emptyList();
        }
    }
    
    /**
     * 查询动态--专门提供给上海车展2015的
     *
     * @param sql
     * @param orderSql
     * @param selectSql
     * @param pageNo
     * @param pageSize
     * @return
     */
    protected Pager<DynaInfo> getDynaListByShangHai2015(SqlBuilder sql, String orderSql, String selectSql, int pageNo, int pageSize) {
        int total = jdbcTemplate.queryForInt(sql.getSql().replace(selectSql, "select count(1) "), sql.getValues());
        if (total == 0) {
            return null;
        }
        Pager<DynaInfo> pager = new Pager<DynaInfo>();
        pager.setPageNo(pageNo);
        pager.setPageSize(pageSize);
        pager.setTotal(total);
        if (total == 0 || pageNo > pager.getPageCount() || (pageNo - 1) * pageSize < 0) {
            return null;
        }
        sql.appendSql(orderSql);
        sql.appendSql(" limit ").appendValue((pageNo - 1) * pageSize).appendSql(",").appendValue(pageSize);

        List<Long> list = jdbcTemplate.query(sql.getSql(), sql.getValues(), new ResultSetExtractor<List<Long>>() {
            @Override
            public List<Long> extractData(ResultSet rs) throws SQLException, DataAccessException {
                List<Long> dynaList = new ArrayList<Long>();
                while (rs.next()) {
                    dynaList.add(rs.getLong("dyna_info_id"));
                }
                return dynaList;
            }
        });

        List<DynaInfo> dynaList = dynaService.findDynaByIds(new Date(), list.toArray());
        if (dynaList.isEmpty()) {
            return null;
        }
        pager.setResultList(dynaList);
        return pager;
    }
    /**
     * 查询话题导语图片
     * @param subjectId
     * @return 
     */
    public List<SubjectPhoto> listSubjectPhoto(long subjectId){
        SqlBuilder sql = new SqlBuilder();
        sql.appendSql("select photo_id from ").appendSql(geliOrm.getTableName(SubjectPhoto.class)).appendSql(" where subject_id=").appendValue(subjectId);
        return geliDao.list(SubjectPhoto.class, sql.getSql(),sql.getValues());
    }
    /**
     * 过滤导语换行
     * @param intr
     * @return 
     */
    public  String filterContent(String intr){
        if(!StringUtils.isBlank(intr)){
           intr = EncodeUtils.encodeForHTMLFilterNull(intr);
           intr = intr.replaceAll("&lt;br&gt;", "<br>");        
           intr = intr.replaceAll("((http|ftp|https)[^\\s]+?)(\\s|(<br>))","<a target='_blank' href='$1'>$1</a>$3");
       }
        return intr;
    }
}
